#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sqlite3
import csv
import cgi

def csv2sql(csvFile):
    connection = sqlite3.connect("teachers.db")
    c = connection.cursor()
    # to prevent records to be duplicated id code is the primary key
    c.execute('''CREATE TABLE IF NOT EXISTS teachers
                (id INTEGER PRIMARY KEY, nome TEXT, grau TEXT, curso TEXT, categoria TEXT, regime TEXT, ano TEXT, tipo_estabelecimento TEXT, nome_estabelecimento TEXT)''')

    spamReader = csv.reader(open(csvFile, 'rb'), delimiter=',', quotechar='"')
    spamReader.next()
    # a ser melhorado 
    # ver se utilizar o r (como se faz com o u) antes dos row[]'s {0} funciona e o codigo escreve os caracteres tal como estão
    # outra possivel solução : csv.QUOTE_ALL
    for row in spamReader:
        stmt = '''INSERT OR REPLACE INTO teachers (id, nome, grau, curso,
                  categoria, regime, ano, tipo_estabelecimento,
                  nome_estabelecimento) VALUES (r"{0}", "{1}", "{2}", "{3}", "{4}", "{5}", "{6}", "{7}", "{8}")'''.\
                  format(row[0], row[1].replace("\\", "\\\\"), row[2].replace("\\", "\\\\"), row[3].replace("\\", "\\\\"),
                         row[4].replace("\\", "\\\\"), row[5].replace("\\", "\\\\"),
                         row[6].replace("\\", "\\\\"), row[7].replace("\\", "\\\\"), row[8].replace("\\", "\\\\"))
        c.execute(stmt)
        connection.commit()
        print row[0]
    pass
   
    connection.close()
    print "completed file ", i
pass


csv2sql('rebcsv/reb0.csv')
